package com.etc.traval.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.etc.traval.biz.DiarycomBiz;
import com.etc.traval.bizimpl.DiarycomBizImpl;
import com.etc.traval.dao.DiaryDao;
import com.etc.traval.dao.DiarycomDao;
import com.etc.traval.dao.PlaceDao;
import com.etc.traval.dao.UserDao;
import com.etc.traval.util.JDBCUtil;
import com.etc.travel.entity.Diary;
import com.etc.travel.entity.User;

import oracle.jdbc.OracleTypes;

public class DiaryDaoImpl implements DiaryDao{
	JDBCUtil util=new JDBCUtil();
	
//添加游记
	@Override
	public boolean addDiary(Diary diary) {
		List<Integer> list=new ArrayList<Integer>();
		list.add(Types.INTEGER);
	Object[] object=util.callHasReturn("{call proc_addDiary(?,?,?,?,?,?,?)}", list,diary.getDiary_title(),diary.getDiary_time(),
			diary.getDiary_user().getUser_id(),diary.getDiary_logo(),diary.getDiary_content(),diary.getDiary_place().getPlace_id());
		util.closeAll();
	int i= (Integer) object[0];
	return i>0;
	}

	//删除游记
	@Override
	public boolean deteleDiaryByID(int id) {
		List<Integer> list=new ArrayList<Integer>();
		list.add(Types.INTEGER);
		Object[] object=util.callHasReturn("{call proc_deleteDiaryByID(?,?)}", list, id);
		int i=(Integer)object[0];
		return i>0;
	}

	//查询游记
	@Override
	public List<Diary> queryDiary(int size,int page) {
		DiarycomDao diarycomDao = new DiarycomDaoImpl();
		List<Diary> list2=new ArrayList<Diary>();
		List<Integer> list=new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] object=util.callHasReturn("{call proc_queryDiary(?,?,?)}",list,size,page);
		ResultSet rs=(ResultSet) object[0];
		PlaceDao place=new PlaceDaoImpl();
		try{
			while(rs.next()){
				Diary diary=new Diary();
				diary.setDiary_id(rs.getInt("diary_id"));
				diary.setDiary_title(rs.getString("diary_title"));
				diary.setDiary_time(rs.getTimestamp("diary_time"));
				List<Integer> list1=new ArrayList<Integer>();
				list1.add(OracleTypes.CURSOR);
				Object[] obj1  =  util.callHasReturn("{call  proc_queryuserbyid(?,?)}", list1, rs.getInt("diary_user"));
				ResultSet rs1 = (ResultSet) obj1[0];
				User user=null;
					if(rs1.next()){
						user = new User();
						user.setUser_id(rs1.getInt("user_id"));
						user.setUser_name(rs1.getString("user_name"));
						user.setUser_pwd(rs1.getString("user_pwd"));
						user.setUser_sex(rs1.getString("user_sex"));
						user.setUser_tel(rs1.getString("user_tel"));
						user.setUser_logo(rs1.getString("user_logo"));
						user.setUser_money(rs1.getDouble("user_money"));
						user.setUser_status(rs1.getString("user_status"));
					}
				diary.setDiary_user(user);
				diary.setDiary_logo(rs.getString("diary_logo"));
				diary.setDiaryComNum(diarycomDao.queryCountByDiary(diary));
				diary.setDiary_content(rs.getString("diary_content"));
				diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
				list2.add(diary);
			}
			
		}catch(Exception e){
			
		}
		finally{
			try {
				
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			util.closeAll();
		}
		return list2;
	}
	
	//查询游记通过用户名字
	@Override
	public List<Diary> queryDiaryByUser(User user,int size,int page) {
	List<Diary> list=new ArrayList<Diary>();
	UserDao user1=new UserDaoImpl();
	ResultSet rs=util.query("select t.*,t.a from (select s.*,rownum as a  from (select * from t_diary where diary_user =?) s where rownum<=?) t where t.a>?",user.getUser_id(),(page*size),((page-1)*size));
	Diary diary=null;
	PlaceDao place=new PlaceDaoImpl();
	try {
		while(rs.next()){
			diary=new Diary();
			diary.setDiary_id(rs.getInt("diary_id"));
			diary.setDiary_title(rs.getString("diary_title"));
			diary.setDiary_time(rs.getTimestamp("diary_time"));
			diary.setDiary_user(user1.queryUserById(rs.getInt("diary_user")));
			diary.setDiary_logo(rs.getString("diary_logo"));
			diary.setDiary_content(rs.getString("diary_content"));
			diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
			list.add(diary);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		util.closeAll();
	}
	return list;
	}

	

//查询游记通过游记名模糊查询
	public List<Diary> queryDiaryByPlaceName(String name,int size,int page) {
	List<Integer> list=new ArrayList<Integer>();
	list.add(OracleTypes.CURSOR);
	Object[] object=util.callHasReturn("{call proc_queryDiaryByPlaceName(?,?,?,?)}", list, name,size,page);
	ResultSet rs=(ResultSet) object[0];
	List<Diary> list2=new ArrayList<Diary>();
	PlaceDao place=new PlaceDaoImpl();
	Diary diary=new Diary();
	try {
		while(rs.next()){
			diary.setDiary_id(rs.getInt("diary_id"));
			diary.setDiary_title(rs.getString("diary_title"));
			diary.setDiary_time(rs.getTimestamp("diary_time"));
			diary.setDiary_logo(rs.getString("diary_logo"));
			diary.setDiary_content(rs.getString("diary_content"));
			diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
            List<Integer> list1=new ArrayList<Integer>();
			list1.add(OracleTypes.CURSOR);
			Object[] obj1  =  util.callHasReturn("{call  proc_queryuserbyid(?,?)}", list1, rs.getInt("diary_user"));
			ResultSet rs1 = (ResultSet) obj1[0];
			User user= null;
				if(rs1.next()){
					user = new User();
					user.setUser_id(rs1.getInt("user_id"));
					user.setUser_name(rs1.getString("user_name"));
					user.setUser_pwd(rs1.getString("user_pwd"));
					user.setUser_sex(rs1.getString("user_sex"));
					user.setUser_tel(rs1.getString("user_tel"));
					user.setUser_logo(rs1.getString("user_logo"));
					user.setUser_money(rs1.getDouble("user_money"));
					user.setUser_status(rs1.getString("user_status"));
				}
				diary.setDiary_user(user);
			list2.add(diary);	
		}
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		util.closeAll();
		
	}	return list2;
	}

	//关键字查询
	public List<Diary> queryLike(String key,int size,int page) { 
		ResultSet rs=util.query("select t.*,t.a from (select s.*,rownum as a  from (select * from t_diary where diary_content like ?) s where rownum<=?) t where t.a>?", "%"+key+"%",(page*size),((page-1)*size));
		List<Diary> list=new ArrayList<Diary>();
		UserDao user=new UserDaoImpl();
		PlaceDao place=new PlaceDaoImpl();
		try {
			while(rs.next()){
				Diary diary=new Diary();
				diary.setDiary_id(rs.getInt("diary_id"));
				diary.setDiary_title(rs.getString("diary_title"));
				diary.setDiary_time(rs.getTimestamp("diary_time"));
				diary.setDiary_user(user.queryUserById(rs.getInt("diary_user")));
				diary.setDiary_logo(rs.getString("diary_logo"));
				diary.setDiary_content(rs.getString("diary_content"));
				diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
				list.add(diary);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			util.closeAll();
		}
		return list;
	}

	public boolean updateDiaryByUserID(String content,int id) {
	int i=util.update("update t_diary set diary_content=? where diary_id=?", content,id);
		return i>0;
	}

//查询游记通过游记ID
	public Diary queryDiaryById(int id) {
		ResultSet rs=util.query("select * from t_diary where diary_id=?", id);
	    Diary diary=new Diary();
	    PlaceDao place=new 	PlaceDaoImpl();
	    UserDao user=new UserDaoImpl();
		try {
			if(rs.next()){
				diary.setDiary_id(rs.getInt("diary_id"));
				diary.setDiary_title(rs.getString("diary_title"));
				diary.setDiary_time(rs.getTimestamp("diary_time"));
				diary.setDiary_user(user.queryUserById(rs.getInt("diary_user")));
				diary.setDiary_logo(rs.getString("diary_logo"));
				diary.setDiary_content(rs.getString("diary_content"));
				diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			util.closeAll();
		}
		return diary;
		}

	/**
	 * 查找游记总数
	 * @author cjw
	 * @return num  游记总数
	 */
	@Override
	public int queryDiaryCount() {
		ResultSet rs = util.query("select count(*) as diarynum from t_diary");
		int num = 0;
		try {
			if(rs.next()){
				num=rs.getInt("diarynum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			util.closeAll();
		}
		return num;
	}

	/**
	 * 查询参与游记评论的人数
	 * @author cjw
	 * @return num  人数
	 */
	@Override
	public int queryComUserNum() {
		ResultSet rs = util.query("select count(distinct diarycom_user) as comnum from t_diarycom");
		int num = 0;
		try {
			if(rs.next()){
				num=rs.getInt("comnum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			util.closeAll();
		}
		return num;
	}

	/**
	 * 通过地点ID查对应游记
	 * @author cjw
	 * @param placeid 地区ID
	 * @return 对应集合
	 */
	@Override
	public List<Diary> queryDiaryByPlaceId(int placeid, int size, int page) {
		List<Integer> list=new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] object=util.callHasReturn("{call proc_queryDiaryByPlaceId(?,?,?,?)}", list, placeid,size,page);
		ResultSet rs=(ResultSet) object[0];
		List<Diary> list2=new ArrayList<Diary>();
		PlaceDao place=new PlaceDaoImpl();
		Diary diary=new Diary();
		try {
			while(rs.next()){
				diary.setDiary_id(rs.getInt("diary_id"));
				diary.setDiary_title(rs.getString("diary_title"));
				diary.setDiary_time(rs.getTimestamp("diary_time"));
				diary.setDiary_logo(rs.getString("diary_logo"));
				diary.setDiary_content(rs.getString("diary_content"));
				diary.setDiary_place(place.queryPlaceById(rs.getInt("diary_place")));
	            List<Integer> list1=new ArrayList<Integer>();
				list1.add(OracleTypes.CURSOR);
				Object[] obj1  =  util.callHasReturn("{call  proc_queryuserbyid(?,?)}", list1, rs.getInt("diary_user"));
				ResultSet rs1 = (ResultSet) obj1[0];
				User user= null;
					if(rs1.next()){
						user = new User();
						user.setUser_id(rs1.getInt("user_id"));
						user.setUser_name(rs1.getString("user_name"));
						user.setUser_pwd(rs1.getString("user_pwd"));
						user.setUser_sex(rs1.getString("user_sex"));
						user.setUser_tel(rs1.getString("user_tel"));
						user.setUser_logo(rs1.getString("user_logo"));
						user.setUser_money(rs1.getDouble("user_money"));
						user.setUser_status(rs1.getString("user_status"));
					}
					diary.setDiary_user(user);
				list2.add(diary);	
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			util.closeAll();
			
		}	return list2;
	}

}